import dbconfig from 'app/src-ssr/db/config';
import mysql from 'mysql2';
const pool = mysql.createPool(dbconfig).promise();

// exports.mysql = pool;

// 把没有``的字段名包起来
function sqlField (field) {
  const reg = /`.*?`/g; // 判断是否已经有``把字段包起来了
  return reg.test(field) ? field : '`' + field + '`';
}

/**
 * @function 插入一条记录
 * @param {string} table 表名
 * @param {object} fieldvalue 字段和值的对象{field1: value1, field2: value2,...}
*/

// exports.insertOne = async (table, fieldvalue) => {
export const insertOne = async (table, fieldvalue) => {
  const fields = [], values = [];
  if (!table || !fieldvalue) { return }

  for (const key in fieldvalue) {
    fields.push(sqlField(key));

    let val;
    if (fieldvalue[key] === null || fieldvalue[key] === undefined) {
      val = 'NULL';
    } else if (typeof fieldvalue[key] === 'string') {
      val = `${pool.escape(fieldvalue[key].replace(/'/g, "\\'"))}`;
    } else if (typeof fieldvalue[key] === 'object') {
      val = `'${JSON.stringify(fieldvalue[key])}'`;
    } else {
      val = `${pool.escape(fieldvalue[key])}`;
    }

    values.push(val);
  }
  let sql = '', result = '';
  try {
    sql = `insert into ${table} (${fields.join(',')}) values (${values.join(',')})`;
    result = await pool.query(sql);
  } catch (e) {
    console.log(e, sql);
  }
  return result;
}

/**
 * @function 批量插入记录
 * @param {string} table 表名
 * @param {array} rows 字段和值的对象[{field1: value1, field2: value2,...}]
*/
// exports.insert = async (table, rows) => {
export const insert = async (table, rows) => {
  let fields = [], values = [];
  if (!table || !rows || Object.prototype.toString.call(rows) !== '[object Array]' || rows.length < 1) { return }

  fields = Object.keys(rows[0]);
  values = rows.map(v => {
    const val = fields.map(f => {
      let fv = ''
      if (v[f] === null || v[f] === undefined) {
        fv = 'NULL';
      } else if (typeof v[f] === 'string') {
        fv = `${pool.escape(v[f].replace(/'/g, "\\'"))}`;
      } else if (typeof v[f] === 'object') {
        fv = `'${JSON.stringify(v[f])}'`;
      } else {
        fv = `${pool.escape(v[f])}`;
      }
      return fv;
    });
    return `(${val.join(',')})`;
  });

  let sql = '', result = '';
  try {
    sql = `insert into ${table} (${fields.map(v => sqlField(v)).join(',')}) values ${values.join(',')}`;
    result = await pool.query(sql);
  } catch (e) {
    console.log(e, sql);
  }
  return result;
}

/**
 * @function 插入或更新记录
 * @param {string} table 表名
 * @param {array} rows 字段和值的对象[{field1: value1, field2: value2,...}]
 * @param {array} update 重复时的更新内容，若不值，则按rows里进行更新，['field1=values(field1)', 'modify_time=UNIX_TIMESTAMP()', ...]
*/
// exports.insertOrUpdate = async (table, rows, update) => {
export const insertOrUpdate = async (table, rows, update) => {
  let fields = [], values = [];
  if (!table || !rows || Object.prototype.toString.call(rows) !== '[object Array]' || rows.length < 1) { return }
  fields = Object.keys(rows[0]);
  values = rows.map(v => {
    const val = fields.map(f => {
      let fv = ''
      if (v[f] === null || v[f] === undefined) {
        fv = 'NULL';
      } else if (typeof v[f] === 'string') {
        fv = `${pool.escape(v[f].replace(/'/g, "\\'"))}`;
      } else if (typeof v[f] === 'object') {
        fv = `'${JSON.stringify(v[f]).replace(/\\/g, '\\\\').replace(/'/g, "\\'")}'`;
      } else {
        fv = `${pool.escape(v[f])}`;
      }
      return fv;
    });
    return `(${val.join(',')})`;
  });

  let sql = '', result = '';
  let updatesql = '';
  if (update) {
    updatesql = update.join(',');
  } else {
    updatesql = fields.map(v => `${sqlField(v)}=VALUES(${sqlField(v)})`).join(',')
  }

  try {
    sql = `INSERT INTO ${table} (${fields.map(v => sqlField(v)).join(',')}) VALUES ${values.join(',')}
    ON DUPLICATE KEY UPDATE ${updatesql}`;
    result = await pool.query(sql);
  } catch (e) {
    console.log(e, sql);
  }
  return result;
}

/**
 * @function 更新记录
 * @param {string} table 表名
 * @param {object} fieldvalue 字段和值的对象{field1: value1, field2: value2,...}
 * @param {object} keys 关键词{id:1,...}
*/
// exports.update = async (table, fieldvalue, keys) => {
export const update = async (table, fieldvalue, keys) => {
  const content = [], condition = [];
  if (!table || !fieldvalue || !keys) { return }

  for (const item in fieldvalue) {
    if (fieldvalue[item] === null || fieldvalue[item] === undefined) {
      content.push(`${sqlField(item)}=NULL`);
    } else if (typeof fieldvalue[item] === 'string') {
      content.push(`${sqlField(item)}=${pool.escape(fieldvalue[item].replace(/'/g, "\\'"))}`);
    } else if (typeof fieldvalue[item] === 'object') {
      content.push(`${sqlField(item)}='${JSON.stringify(fieldvalue[item])}'`);
    } else {
      content.push(`${sqlField(item)}=${pool.escape(fieldvalue[item])}`);
    }
  }

  for (const k in keys) {
    if (typeof keys[k] === 'string') {
      condition.push(`${sqlField(k)}=${pool.escape(keys[k].replace(/'/g, "\\'"))}`);
    } else {
      condition.push(`${sqlField(k)}=${pool.escape(keys[k])}`);
    }
  }
  let sql = '', result = '';
  try {
    sql = `update ${table} set ${content.join(',')} where ${condition.join(' and ')}`;
    result = await pool.query(sql);
  } catch (e) {
    console.log(e, sql);
  }

  return result;
}

/**
 * @function 删除记录
 * @param {string} table 表名
 * @param {object} keys 关键词{id:1,...}
*/
// exports.delete = async (table, keys) => {
async function deleteRow (table, keys) {
  const condition = [];
  if (!table || !keys) { return }

  for (const k in keys) {
    if (typeof keys[k] === 'string') {
      condition.push(`${sqlField(k)}=${pool.escape(keys[k].replace(/'/g, "\\'"))}`);
    } else {
      condition.push(`${sqlField(k)}=${pool.escape(keys[k])}`);
    }
  }
  let sql = '', result = '';
  try {
    sql = `delete from ${table} where ${condition.join(' and ')}`;
    result = await pool.query(sql);
  } catch (e) {
    console.log(e, sql);
  }

  return result;
}

/**
 * @function select数据
 * @param {string} table 表名
 * @param {array} fields 字段名
 * @param {object} keys 取数据的条件
 * @param {boolean} one 是否取一条数据
 * @returns 若one=true，返回object，若one=false，返回array
 */
// exports.select = async (table, fields, keys, one) => {
export const select = async (table, fields, keys, one) => {
  const condition = [];
  if (!table || !fields) { return }

  for (const k in keys) {
    if (typeof keys[k] === 'string') {
      condition.push(`${k}='${keys[k].replace(/'/g, "\\'")}'`);
    } else {
      condition.push(`${k}='${keys[k]}'`);
    }
  }
  let sql = '', rows = [];
  try {
    sql = `select ${fields.join(',')} from ${table} ${condition.length > 0 ? ' where ' + condition.join(' and ') : ''}`;
    [rows] = await pool.query(sql);
  } catch (e) {
    console.log(e, sql);
  }

  if (one) { // 只取第一条
    return rows.length > 0 ? rows[0] : {};
  } else {
    return rows;
  }
};

/**
 * 校正表的的auto_increment
 * @param {string} table 表名
 * @param {string} field 自增长字段
 * @param {boolean} negative 是否为负增长 true-是 false-否
 */
// exports.checkAutoIncrement = async (table, field, negative = true) => {
export const checkAutoIncrement = async (table, field, negative = true) => {
  const [scheme] = await pool.execute('SELECT auto_increment FROM information_schema.tables WHERE table_schema=? and table_name=?', [dbconfig.database, table]);
  const [rows] = await pool.query(`SELECT ${negative ? 'MIN' : 'MAX'}(??) id FROM ??`, [field, table]);

  if (scheme.length > 0) {
    const schemeMax = scheme[0].AUTO_INCREMENT;
    const rowsMax = (rows[0].id || 0) * (negative ? -1 : 1);
    if (schemeMax <= rowsMax) {
      await pool.query('ALTER TABLE ?? AUTO_INCREMENT = ?', [table, rowsMax + 1]);
    }
  }
};

export { pool as mysql, deleteRow as delete };
export default {
  insertOne,
  insert,
  insertOrUpdate,
  update,
  delete: deleteRow,
  select,
  checkAutoIncrement
};
